package com.neusoft.neuiotms.dcom.service.insurance.impl;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigInteger;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.persistence.EntityManager;
import javax.persistence.EntityNotFoundException;
import javax.persistence.PersistenceContext;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.gridfs.GridFsTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.DigestUtils;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.neusoft.bizcore.auth.common.bean.UserBean;
import com.neusoft.bizcore.file.converter.MongoFileConverter;
import com.neusoft.bizcore.file.dto.FileDTO;
import com.neusoft.bizcore.file.model.FileDocument;
import com.neusoft.bizcore.file.service.FileService;
import com.neusoft.bizcore.notice.notification.dto.NoticeRecordDTO;
import com.neusoft.bizcore.notice.notification.dto.NoticeRecordSystemDTO;
import com.neusoft.bizcore.web.constant.ExceptionConstants;
import com.neusoft.bizcore.web.exception.UnitedException;
import com.neusoft.bizcore.web.support.Searchable;
import com.neusoft.bizcore.web.utils.EasyPoiStylerColorImpl;
import com.neusoft.bizcore.web.utils.JsonUtils;
import com.neusoft.neuiotms.dcom.constants.DCOMConstants;
import com.neusoft.neuiotms.dcom.converter.insurance.InsuranceInspectionTaskConverter;
import com.neusoft.neuiotms.dcom.converter.insurance.InsuranceRegisterTaskConverter;
import com.neusoft.neuiotms.dcom.converter.insurance.InsuranceReportTaskConverter;
import com.neusoft.neuiotms.dcom.converter.insurance.InsuranceSummaryTaskConverter;
import com.neusoft.neuiotms.dcom.dto.InstitutionsDTO;
import com.neusoft.neuiotms.dcom.dto.insurance.InsuranceInspectionTaskDTO;
import com.neusoft.neuiotms.dcom.dto.insurance.InsuranceRegisterTaskDTO;
import com.neusoft.neuiotms.dcom.dto.insurance.InsuranceReportTaskDTO;
import com.neusoft.neuiotms.dcom.dto.insurance.InsuranceSummaryTaskDTO;
import com.neusoft.neuiotms.dcom.model.Institutions;
import com.neusoft.neuiotms.dcom.model.ResidentUser;
import com.neusoft.neuiotms.dcom.model.enums.InsuranceInspectionTaskType;
import com.neusoft.neuiotms.dcom.model.enums.InsuranceReportTaskStatus;
import com.neusoft.neuiotms.dcom.model.enums.InsuranceTaskStatus;
import com.neusoft.neuiotms.dcom.model.insurance.InsuranceInspectionTask;
import com.neusoft.neuiotms.dcom.model.insurance.InsuranceRegisterTask;
import com.neusoft.neuiotms.dcom.model.insurance.InsuranceReportTask;
import com.neusoft.neuiotms.dcom.model.insurance.InsuranceSummaryTask;
import com.neusoft.neuiotms.dcom.repository.InstitutionsRepository;
import com.neusoft.neuiotms.dcom.repository.insurance.InsuranceInspectionTaskRepository;
import com.neusoft.neuiotms.dcom.repository.insurance.InsuranceRegisterTaskRepository;
import com.neusoft.neuiotms.dcom.repository.insurance.InsuranceReportTaskRepository;
import com.neusoft.neuiotms.dcom.repository.insurance.InsuranceSummaryTaskFilesRepository;
import com.neusoft.neuiotms.dcom.repository.insurance.InsuranceSummaryTaskRepository;
import com.neusoft.neuiotms.dcom.service.InstitutionsService;
import com.neusoft.neuiotms.dcom.service.insurance.InsuranceRegisterTaskService;
import com.neusoft.neuiotms.micro.notice.NoticeMicroService;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;

@Service
@Slf4j
public class InsuranceRegisterTaskServiceImpl implements InsuranceRegisterTaskService {

    @Value("${files.storage.mongodb.collection:files}")
    private String collectionName;

    @Autowired
    private InsuranceRegisterTaskRepository taskRepository;
    @Autowired
    private InsuranceRegisterTaskConverter taskConverter;

    @Autowired
    private InsuranceInspectionTaskRepository inspectionRepository;
    @Autowired
    private InsuranceInspectionTaskConverter inspectionConverter;

    @Autowired
    private InsuranceReportTaskRepository reportRepository;
    @Autowired
    private InsuranceReportTaskConverter reportConverter;

    @Autowired
    private InsuranceSummaryTaskRepository summaryRepository;
    @Autowired
    private InsuranceSummaryTaskConverter summaryConverter;
    @Autowired
    private InsuranceSummaryTaskFilesRepository summaryFilesRepository;

    @Autowired
    private InstitutionsRepository institutionsRepository;
    //    @Autowired
    //    private OperationGroupRepository operationGroupRepository;
    @Autowired
    private NoticeMicroService noticeMicroService;
    @Autowired
    private MongoTemplate mongoTemplate;
    @Autowired
    private MongoFileConverter mongoFileConverter;
    @Autowired
    private GridFsTemplate gridFsTemplate;
    @Autowired
    private FileService fileService;
    @PersistenceContext
    private EntityManager entityManager;
    @Autowired
    private InstitutionsService institutionsService;

    @Override
    public List<InsuranceRegisterTaskDTO> index(final Searchable searchable, final UserBean user) {
        if (user.getRoles().stream()
                .filter(it -> DCOMConstants.ROLE_ADMIN.equals(it.getRole())
                        || DCOMConstants.ROLE_WORKER_GROUP_LEADER.equals(it.getRole())
                        || DCOMConstants.ROLE_WORKER.equals(it.getRole()))
                .findAny()
                .isPresent()) {
            final List<InstitutionsDTO> institutions = this.institutionsService.getPermInstitutions(user);

            final List<String> unitCodeArray = institutions.stream().map(InstitutionsDTO::getCode)
                    .collect(Collectors.toList());
            if (null == searchable.getStrArray("unitCodes")) {
                searchable.put("unitCodes", unitCodeArray);
            }
            final List<InsuranceRegisterTask> models = this.taskRepository.index(searchable);
            return this.taskConverter.toListDTO(models);

        } else {
            return new ArrayList<>();
        }

        //        // 判断是否为运维组长
        //        final OperationGroup operationGroupModel =
        //                this.operationGroupRepository.findByGroupLeaderCode(user.getUsername());
        //        if (null != operationGroupModel) {
        //            // 此账号为运维组长
        //            final List<String> unitCodes = operationGroupModel.getInstitutions().stream().map(Institutions::getCode)
        //                    .collect(Collectors.toList());
        //            if (null == searchable.getStrArray("unitCodes")) {
        //                searchable.put("unitCodes", unitCodes);
        //            }
        //            final List<InsuranceRegisterTask> models = this.taskRepository.index(searchable);
        //            return this.taskConverter.toListDTO(models);
        //        } else {
        //            // 是否为一线运维工程师
        //            List<InsuranceRegisterTaskDTO> dtos = new ArrayList<>();
        //            final Institutions institutionModel =
        //                    this.institutionsRepository.findByResidents_UsernameIn(Arrays.asList(user.getUsername()));
        //            if (null != institutionModel) {
        //                if (StringUtils.isEmpty(searchable.getStrValue("unitCode"))) {
        //                    searchable.put("unitCode", institutionModel.getCode());
        //                }
        //                // 一线工程师
        //                final List<InsuranceRegisterTask> models = this.taskRepository.index(searchable);
        //                dtos = this.taskConverter.toListDTO(models);
        //            }
        //            return dtos;
        //        }
    }

    @Override
    public Page<InsuranceRegisterTaskDTO> page(final Searchable searchable, final Pageable pageable,
            final UserBean user) {

        if (user.getRoles().stream()
                .filter(it -> DCOMConstants.ROLE_ADMIN.equals(it.getRole())
                        || DCOMConstants.ROLE_WORKER_GROUP_LEADER.equals(it.getRole())
                        || DCOMConstants.ROLE_WORKER.equals(it.getRole()))
                .findAny()
                .isPresent()) {
            final List<InstitutionsDTO> institutions = this.institutionsService.getPermInstitutions(user);

            final List<String> unitCodeArray = institutions.stream().map(InstitutionsDTO::getCode)
                    .collect(Collectors.toList());
            if (null == searchable.getStrArray("unitCodes")) {
                searchable.put("unitCodes", unitCodeArray);
            }
            final Page<InsuranceRegisterTask> models = this.taskRepository.search(pageable, searchable);
            return this.taskConverter.toPageDTO(models);

        } else {
            return new PageImpl<>(new ArrayList<InsuranceRegisterTaskDTO>());
        }

        //        // 判断是否为运维组长
        //        final OperationGroup operationGroupModel =
        //                this.operationGroupRepository.findByGroupLeaderCode(user.getUsername());
        //        if (null != operationGroupModel) {
        //            // 此账号为运维组长
        //            final List<String> unitCodes = operationGroupModel.getInstitutions().stream().map(Institutions::getCode)
        //                    .collect(Collectors.toList());
        //            if (null == searchable.getStrArray("unitCodes")) {
        //                searchable.put("unitCodes", unitCodes);
        //            }
        //            final Page<InsuranceRegisterTask> models = this.taskRepository.search(pageable, searchable);
        //            return this.taskConverter.toPageDTO(models);
        //        } else {
        //            // 是否为一线运维工程师
        //            Page<InsuranceRegisterTaskDTO> dtos = new PageImpl<>(new ArrayList<InsuranceRegisterTaskDTO>());
        //            final Institutions institutionModel =
        //                    this.institutionsRepository.findByResidents_UsernameIn(Arrays.asList(user.getUsername()));
        //            if (null != institutionModel) {
        //                if (StringUtils.isEmpty(searchable.getStrValue("unitCode"))) {
        //                    searchable.put("unitCode", institutionModel.getCode());
        //                }
        //                // 一线工程师
        //                final Page<InsuranceRegisterTask> models = this.taskRepository.search(pageable, searchable);
        //                dtos = this.taskConverter.toPageDTO(models);
        //            }
        //            return dtos;
        //        }
    }

    @Override
    public InsuranceRegisterTaskDTO update(final Long id, final InsuranceRegisterTaskDTO dto, final UserBean user) {
        InsuranceRegisterTask model;
        try {
            model = this.taskRepository.getOne(id);
        } catch (final EntityNotFoundException e) {
            InsuranceRegisterTaskServiceImpl.log.error(e.getMessage(), e);
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        dto.setId(model.getId());
        model = this.taskConverter.toModel(dto);
        this.taskRepository.save(model);
        return this.taskConverter.toDTO(model);
    }

    @Override
    public InsuranceRegisterTaskDTO detail(final Long id) {
        try {
            final InsuranceRegisterTask model = this.taskRepository.getOne(id);
            return this.taskConverter.toDTO(model);
        } catch (final EntityNotFoundException e) {
            InsuranceRegisterTaskServiceImpl.log.error(e.getMessage(), e);
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
    }

    @Override
    public void delete(final Long id) {
        // TODO Auto-generated method stub
    }

    // -------------------------- 重保服务巡检任务 --------------------------------
    @Override
    public List<InsuranceInspectionTaskDTO> inspectionIndex(final Searchable searchable) {
        final List<InsuranceInspectionTask> model = this.inspectionRepository.index(searchable);
        return this.inspectionConverter.toListDTO(model);
    }

    @Override
    public InsuranceInspectionTaskDTO inspectionUpdate(final Long id, final InsuranceInspectionTaskDTO dto,
            final UserBean user) {
        InsuranceInspectionTask model;
        try {
            model = this.inspectionRepository.findByRegisterTaskIdAndType(id,
                    InsuranceInspectionTaskType.valueOf(dto.getType()));
        } catch (final EntityNotFoundException e) {
            InsuranceRegisterTaskServiceImpl.log.error(e.getMessage(), e);
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        dto.setExecutorCode(user.getUsername());
        dto.setExecutorName(user.getName());
        dto.setId(model.getId());
        model = this.inspectionConverter.toModel(dto);
        this.inspectionRepository.save(model);
        return this.inspectionConverter.toDTO(model);
    }

    @Override
    public InsuranceInspectionTaskDTO inspectionSubmit(final Long id, final InsuranceInspectionTaskDTO dto,
            final UserBean user) {
        InsuranceInspectionTask model;
        try {
            model = this.inspectionRepository.findByRegisterTaskIdAndType(id,
                    InsuranceInspectionTaskType.valueOf(dto.getType()));
        } catch (final EntityNotFoundException e) {
            InsuranceRegisterTaskServiceImpl.log.error(e.getMessage(), e);
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        dto.setExecutorCode(user.getUsername());
        dto.setExecutorName(user.getName());
        dto.setStatus(InsuranceTaskStatus.completed.name());
        dto.setId(model.getId());
        model = this.inspectionConverter.toModel(dto);
        this.inspectionRepository.save(model);

        // 是否触发服务总结
        if (((model.getStatus() == InsuranceTaskStatus.completed)
                && (model.getType() == InsuranceInspectionTaskType.after))
                && (model.getType() == InsuranceInspectionTaskType.after)) {
            final InsuranceSummaryTask summaryTaskModel =
                    this.summaryRepository.findByRegisterTaskId(model.getRegisterTask().getId());
            summaryTaskModel.setStatus(InsuranceTaskStatus.pending);
            this.summaryRepository.save(summaryTaskModel);
            // 获取所属单位的一线运维工程师
            final Institutions institutionsModel =
                    this.institutionsRepository.findByCode(summaryTaskModel.getUnitCode());
            final List<String> userNames = institutionsModel.getResidents().stream().map(ResidentUser::getUsername)
                    .collect(Collectors.toList());
            // 发送通知
            if (!userNames.isEmpty()) {
                final NoticeRecordDTO notice = new NoticeRecordDTO();
                notice.setTitle("重保服务任务");
                notice.setType(Lists.newArrayList(DCOMConstants.INFORM_TYPE_NOTICE));
                notice.setAppCode("dcom");
                final NoticeRecordSystemDTO sys = new NoticeRecordSystemDTO();
                sys.setContent("您有服务总结的巡检任务");
                sys.setNoticeType("重保服务任务：服务总结");
                //                sys.setNoticeRoute("registerTask");
                final JSONObject info = new JSONObject();
                sys.setBusinessInfo(info.toString());
                sys.setUsers(userNames);
                notice.setSystem(sys);
                this.noticeMicroService.createNotification(JsonUtils.pojoToJson(notice));
            } else {
                InsuranceRegisterTaskServiceImpl.log.error("重保服务通知用户为null");
            }
        }

        return this.inspectionConverter.toDTO(model);
    }

    @Override
    public InsuranceInspectionTaskDTO inspectionDetail(final Long registerTaskId, final String type) {
        try {
            final InsuranceInspectionTask model =
                    this.inspectionRepository.findByRegisterTaskIdAndType(registerTaskId,
                            InsuranceInspectionTaskType.valueOf(type));
            return this.inspectionConverter.toDTO(model);
        } catch (final EntityNotFoundException e) {
            InsuranceRegisterTaskServiceImpl.log.error(e.getMessage(), e);
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {registerTaskId, type });
        }
    }

    // -------------------------- 每日上报任务 --------------------------------
    @Override
    public List<InsuranceReportTaskDTO> reportIndex(final Searchable searchable) {
        final List<InsuranceReportTask> model = this.reportRepository.index(searchable);
        return this.reportConverter.toListDTO(model);
    }

    @Override
    public InsuranceReportTaskDTO reportUpdate(final Long id, final InsuranceReportTaskDTO dto,
            final UserBean user) {
        InsuranceReportTask model;
        try {
            model = this.reportRepository.getOne(id);
        } catch (final EntityNotFoundException e) {
            InsuranceRegisterTaskServiceImpl.log.error(e.getMessage(), e);
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        dto.setReportPersonCode(user.getUsername());
        dto.setReportPersonName(user.getName());
        dto.setStatus(InsuranceReportTaskStatus.reported.name());
        dto.setId(model.getId());
        model = this.reportConverter.toModel(dto);
        this.reportRepository.save(model);
        return this.reportConverter.toDTO(model);
    }

    @Override
    public List<InsuranceReportTaskDTO> reportDetail(final Long registerTaskId) {
        try {
            final List<InsuranceReportTask> model = this.reportRepository.findByRegisterTaskId(registerTaskId);
            return this.reportConverter.toListDTO(model);
        } catch (final EntityNotFoundException e) {
            InsuranceRegisterTaskServiceImpl.log.error(e.getMessage(), e);
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {registerTaskId });
        }
    }

    // -------------------------- 服务总结任务 --------------------------------
    @Override
    public List<InsuranceSummaryTaskDTO> summaryIndex(final Searchable searchable) {
        final List<InsuranceSummaryTask> model = this.summaryRepository.index(searchable);
        return this.summaryConverter.toListDTO(model);
    }

    @Override
    public InsuranceSummaryTaskDTO summaryUpdate(final Long id, final InsuranceSummaryTaskDTO dto,
            final UserBean user) {
        InsuranceSummaryTask model;
        try {
            model = this.summaryRepository.findByRegisterTaskId(id);
        } catch (final EntityNotFoundException e) {
            InsuranceRegisterTaskServiceImpl.log.error(e.getMessage(), e);
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        // 判断是否可以提交重保服务，提交前提所有任务是否已完成状态
        final InsuranceRegisterTask taskModel = this.taskRepository.getOne(id);
        final InsuranceRegisterTaskDTO taskDto = this.taskConverter.toDTO(taskModel);
        if ((taskDto.getBeforeInspectionStatus() != InsuranceTaskStatus.completed.name())
                || (taskDto.getAfterInspectionStatus() != InsuranceTaskStatus.completed.name())
                || (taskDto.getTotalReportedNum() != taskDto.getCompleteReportedNum())) {
            throw new UnitedException("前面有任务未完成，请完成后，再提交服务总结！");
        }
        dto.setReportPersonCode(user.getUsername());
        dto.setReportPersonName(user.getName());
        dto.setStatus(InsuranceTaskStatus.completed.name());
        dto.setId(model.getId());
        model = this.summaryConverter.toModel(dto);
        this.summaryRepository.save(model);
        // 更新任务总表状态
        taskModel.setStatus(InsuranceTaskStatus.completed);
        this.taskRepository.save(taskModel);
        return this.summaryConverter.toDTO(model);
    }

    @Override
    public InsuranceSummaryTaskDTO summaryDetail(final Long registerTaskId) {
        try {
            final InsuranceSummaryTask model = this.summaryRepository.findByRegisterTaskId(registerTaskId);
            return this.summaryConverter.toDTO(model);
        } catch (final EntityNotFoundException e) {
            InsuranceRegisterTaskServiceImpl.log.error(e.getMessage(), e);
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {registerTaskId });
        }
    }

    @Override
    public FileDTO summaryUpload(final MultipartFile file) {
        try {
            final String md5 = DigestUtils.md5DigestAsHex(file.getInputStream());
            FileDocument doc = this.getByMd5(md5);
            if (doc != null) {
                return this.mongoFileConverter.toDTO(doc);
            }

            doc = new FileDocument();
            doc.setName(file.getOriginalFilename());
            doc.setSize(file.getSize());
            doc.setContentType(file.getContentType());
            doc.setUploadDate(new Date());
            doc.setMd5(md5);
            final String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
            doc.setSuffix(suffix);

            final String fsId = this.uploadFileToGridFS(file.getInputStream(), this.createFilePath(suffix), suffix);
            doc.setFsId(fsId);
            doc = this.mongoTemplate.save(doc, this.collectionName);
            return this.mongoFileConverter.toDTO(doc);
        } catch (final IOException e) {
            InsuranceRegisterTaskServiceImpl.log.error("", e);
        }
        return null;
    }

    @Override
    public void deleteMongoFile(final String fsId) {
        this.fileService.deleteByFsId(fsId);
        this.summaryFilesRepository.deleteByFsId(fsId);
    }

    private String uploadFileToGridFS(final InputStream in, final String fileName, final String contentType) {
        this.gridFsTemplate.store(in, fileName, contentType);
        return fileName;
    }

    private FileDocument getByMd5(final String md5) {
        final Query query = new Query().addCriteria(Criteria.where("md5").is(md5));
        final FileDocument fileDocument = this.mongoTemplate.findOne(query, FileDocument.class, this.collectionName);
        return fileDocument;
    }

    private String createFilePath(final String suffix) {
        final StringBuilder id = new StringBuilder();
        id.append(String.valueOf(System.currentTimeMillis()))
                .append(String.valueOf((int) (Math.random() * 10000)))
                .append(".")
                .append(suffix);
        return id.toString();
    }

    @SuppressWarnings("unchecked")
    @Override
    public Page<Map<String, Object>> report(Pageable pageable, final Map<String, Object> params) {

        final String countSql = this.buildTotalSql(params);
        InsuranceRegisterTaskServiceImpl.log.info(countSql);
        final javax.persistence.Query query1 = this.entityManager.createNativeQuery(countSql);
        query1.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result1 = query1.getResultList();
        int total = 0;
        if (result1.size() > 0) {
            total = Integer.parseInt(String.valueOf(result1.get(0).get("count")));
        }

        final String sql = this.buildQuerySql(pageable, params);
        InsuranceRegisterTaskServiceImpl.log.info(sql);
        final javax.persistence.Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();
        for (final Map<String, Object> map : result) {
            final Date startTime = (Date) map.get("start_time");
            final Date endTime = (Date) map.get("end_time");

            final String sqlWorkorder = this.buildWorkorderStatsSql(startTime, endTime);
            InsuranceRegisterTaskServiceImpl.log.info(sqlWorkorder);
            final javax.persistence.Query queryWorkorder = this.entityManager.createNativeQuery(sqlWorkorder);
            query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            final BigInteger totalWorkorders = (BigInteger) queryWorkorder.getSingleResult();
            map.put("orders", totalWorkorders.intValue());
        }
        return new PageImpl<>(result, pageable, total);
    }

    @SuppressWarnings("unchecked")
    @Override
    public Workbook exportReport(final Map<String, Object> params) {
        final String sql = this.buildExportSql(params);
        InsuranceRegisterTaskServiceImpl.log.info(sql);
        final javax.persistence.Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();

        final List<ReportVO> list = new ArrayList<>(result.size() == 0 ? 1 : result.size());
        result.stream().map(t -> {
            final ReportVO vo = new ReportVO();
            vo.setUnit_name(t.get("unit_name"));
            vo.setDays(t.get("days"));
            return vo;
        }).forEach(list::add);

        final ExportParams param =
                new ExportParams("重保服务统计", "重保服务统计", ExcelType.XSSF);
        param.setStyle(EasyPoiStylerColorImpl.class);
        return ExcelExportUtil.exportExcel(param, ReportVO.class, list);
    }

    private String buildTotalSql(final Map<String, Object> params) {
        final String statement = " count(*) as count ";
        return this.buildSql(params, statement);
    }

    private String buildQuerySql(Pageable pageable, final Map<String, Object> params) {
        final String statement = " * ";
        String noPageSql = this.buildSql(params, statement);
        noPageSql += " limit " + pageable.getPageSize() + " offset " + pageable.getOffset();

        return noPageSql;
    }

    private String buildExportSql(final Map<String, Object> params) {
        final String statement = " * ";
        return this.buildSql(params, statement);
    }

    private String buildSql(final Map<String, Object> params, String statement) {
        String where = " 1=1 ";
        if (params.containsKey("unit_code")) {
            where += "and t0.unit_code='" + (String) params.get("unit_code") + "' ";
        }
        if (params.containsKey("start")) {
            where += "and t0.start_time>='" + (String) params.get("start") + "' ";
        }
        if (params.containsKey("end")) {
            Date end = null;
            try {
                end = DateUtils.parseDate((String) params.get("end"), "yyyy-MM-dd");
            } catch (final ParseException e) {
            }
            if (null != end) {
                final Calendar c = Calendar.getInstance();
                c.setTime(end);
                c.add(Calendar.DAY_OF_MONTH, 1);
                where += "and t0.start_time<'" + DateFormatUtils.format(c.getTime(), "yyyy-MM-dd") + "' ";
            }
        }

        //        String sql = "SELECT "
        //                + statement
        //                + "FROM ( "
        //                + "SELECT "
        //                + " unit_code, "
        //                + " unit_name, "
        //                + " sum(days) AS days "
        //                + "FROM "
        //                + " ( "
        //                + "     SELECT "
        //                + "         unit_code, "
        //                + "         unit_name, "
        //                + "         datediff(end_time, start_time) days "
        //                + "     FROM "
        //                + "         neuiot_insurance_register_task t0 "
        //                + "     %s "
        //                + " ) t "
        //                + "GROUP BY "
        //                + " unit_code, "
        //                + " unit_name "
        //                + ") t";

        String sql = "SELECT "
                + statement
                + "FROM "
                + "  ( "
                + "    SELECT "
                + "      start_time, "
                + "      end_time, "
                + "      unit_code, "
                + "      unit_name, "
                + "      sum(days) AS days"
                //                + "      sum(orders) AS orders "
                + "    FROM "
                + "      ( "
                + "        SELECT "
                + "          t0.unit_code, "
                + "          t0.unit_name, "
                + "          t0.start_time, "
                + "          t0.end_time, "
                + "          datediff(t0.end_time, t0.start_time) days, "
                + "          count(*) AS orders "
                + "        FROM "
                + "          neuiot_insurance_register_task t0 "
                + "        LEFT JOIN neuiot_institutions t1 ON t0.unit_code = t1. CODE "
                + "        LEFT JOIN neuiot_resident_user t2 ON t1.id = t2.institutions_id "
                //                + "        LEFT JOIN ( "
                //                + "          SELECT "
                //                + "            work_num, "
                //                + "            frontline_engineer_code "
                //                + "          FROM "
                //                + "            neuiot_malfunction_apply_form "
                //                + "          GROUP BY "
                //                + "            work_num, "
                //                + "            frontline_engineer_code "
                //                + "        ) t3 ON t2.username = t3.frontline_engineer_code "
                //                + "        LEFT JOIN wf_work_order t4 ON t3.work_num = t4.work_order_num "
                + "        WHERE "
                + "        %s "
                //                + "        AND t4.type = 'malfunction' "
                //                + "        AND t4.create_time > t0.start_time "
                //                + "        AND t4.update_time < t0.end_time "
                //                + "        AND t4. STATUS = 'finish' "
                + "        GROUP BY "
                + "          t0.unit_code, "
                + "          t0.unit_name, "
                + "          t0.end_time, "
                + "          t0.start_time "
                + "      ) t "
                + "    GROUP BY "
                + "      unit_code, "
                + "      unit_name, "
                + "      start_time, "
                + "      end_time "
                + "  ) t ";

        sql = sql.replace("%s", where);

        return sql;
    }

    private String buildWorkorderStatsSql(Date start, Date end) {
        final String sql = " SELECT"
                + "  count(*) AS total"
                + " FROM"
                + "  neuiot_malfunction_apply_form t3"
                + " LEFT JOIN wf_work_order t4 ON t3.work_num = t4.work_order_num"
                + " WHERE"
                + "  t4.type = 'malfunction'"
                + " AND t4. STATUS = 'finish'"
                + " AND t4.create_time > '" + start + "'"
                + " AND t4.update_time < '" + end + "'";

        return sql;
    }

    @Setter
    @Getter
    static class ReportVO {

        @Excel(name = "服务厅局", orderNum = "0", width = 30)
        private Object unit_name;

        @Excel(name = "服务天数（天）", orderNum = "1", width = 30)
        private Object days;

    }

}
